package cj.web.marriage.dao;
/*
 *  
 *  
*/
import java.util.List;
import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.marriage.domain.MemberDetail;

/**
 * 
 * @author 
 *
 */
@DAO
public interface MemberDetailDAO{
	
	@SQL("SELECT a.*,b.phone as account,c.name as companyNameGL,DATE_FORMAT(NOW(), '%Y') - SUBSTRING(a.identityCard, 7, 4) AS age FROM MemberDetail a join SCBuyer b on a.userId=b.id left join Company c on a.companyId = c.id WHERE a.deleteFlag=1 #if(:t.status!=-1){ AND a.status =:t.status } #if(:t.profession!=''){ AND a.profession like '%##(:t.profession)%'} #if(:t.money!=-1){ AND a.money = :t.money } #if(:t.height!=''){ AND a.height like '%##(:t.height)%'} #if(:t.education!=-1){ AND a.education = :t.education } #if(:t.sex!=-1){ AND a.sex = :t.sex } #if(:t.account!=''){ AND b.phone like '%##(:t.account)%'} #if(:t.name!=''){ AND (a.name like '%##(:t.name)%' or a.nickname like '%##(:t.name)%')} #if(:t.companyId!=-1){ AND a.companyId like '%##(:t.companyId)%'} ORDER BY a.updateAt DESC LIMIT :t.start,:t.length ")
    List<MemberDetail> query(@SQLParam("t") MemberDetail memberDetail);
		
	@SQL("SELECT count(1) FROM MemberDetail a join SCBuyer b on a.userId=b.id left join Company c on a.companyId = c.id WHERE a.deleteFlag=1 #if(:t.status!=-1){ AND a.status =:t.status } #if(:t.profession!=''){ AND a.profession like '%##(:t.profession)%'} #if(:t.money!=-1){ AND a.money = :t.money } #if(:t.height!=''){ AND a.height like '%##(:t.height)%'} #if(:t.education!=-1){ AND a.education = :t.education } #if(:t.sex!=-1){ AND a.sex = :t.sex } #if(:t.account!=''){ AND b.phone like '%##(:t.account)%'} #if(:t.name!=''){ AND (a.name like '%##(:t.name)%' or a.nickname like '%##(:t.name)%')} #if(:t.companyId!=-1){ AND a.companyId like '%##(:t.companyId)%'} ")
    int querySize(@SQLParam("t") MemberDetail memberDetail);
    
    @SQL("SELECT a.*,b.phone as account,c.name as companyNameGL FROM MemberDetail a join SCBuyer b on a.userId=b.id left join Company c on a.companyId = c.id WHERE a.id=:1")
    MemberDetail queryById(int id);
    
    @SQL("SELECT a.*,b.phone as account,c.name as companyNameGL FROM MemberDetail a join SCBuyer b on a.userId=b.id left join Company c on a.companyId = c.id WHERE a.deleteflag=1 and a.userId=:1")
    List<MemberDetail> queryByUserId(int userId);
    
    @SQL("INSERT INTO MemberDetail ("
			        		+ "userId,"
			        		+ "type,"
			        		+ "companyId,"
			        		+ "companyName,"
			        		+ "name,"
			        		+ "identityCard,"
			        		+ "photo,"
			        		+ "nickname,"
			        		+ "sex,"
			        		+ "isMarry,"
			        		+ "weight,"
			        		+ "height,"
			        		+ "isOpen,"
			        		+ "education,"
			        		+ "school,"
			        		+ "profession,"
			        		+ "money,"
			        		+ "roomOrCar,"
			        		+ "isChildren,"
			        		+ "infor,"
			        		+ "selection,"
			        		+ "province,"
			        		+ "city,"
			        		+ "area,"
			        		+ "status,"
			        		+ "deleteFlag,"
			        		+ "createUser,"
			        		+ "createAt,"
			        		+ "updateUser,"
			        		+ "updateAt,"
			        		+ "followCount"
							+") VALUES ("
							+ ":t.userId,"
							+ ":t.type,"
							+ ":t.companyId,"
							+ ":t.companyName,"
							+ ":t.name,"
							+ ":t.identityCard,"
							+ ":t.photo,"
							+ ":t.nickname,"
							+ ":t.sex,"
							+ ":t.isMarry,"
							+ ":t.weight,"
							+ ":t.height,"
							+ ":t.isOpen,"
							+ ":t.education,"
							+ ":t.school,"
							+ ":t.profession,"
							+ ":t.money,"
							+ ":t.roomOrCar,"
							+ ":t.isChildren,"
							+ ":t.infor,"
							+ ":t.selection,"
							+ ":t.province,"
							+ ":t.city,"
							+ ":t.area,"
							+ ":t.status,"
							+ "1,"
							+ ":t.createUser,"
							+ "now(),"
							+ ":t.updateUser,"
							+ "now(),"
							+ "0"
					+")")
    void create(@SQLParam("t") MemberDetail memberDetail);
	
	@SQL("UPDATE MemberDetail SET "
	    			        + "type=:t.type,"
	    			        + "companyId=:t.companyId,"
	    			        + "companyName=:t.companyName,"
	    			        + "name=:t.name,"
	    			        + "identityCard=:t.identityCard,"
	    			        + "photo=:t.photo,"
	    			        + "nickname=:t.nickname,"
	    			        + "sex=:t.sex,"
	    			        + "isMarry=:t.isMarry,"
	    			        + "weight=:t.weight,"
	    			        + "height=:t.height,"
	    			        + "isOpen=:t.isOpen,"
	    			        + "education=:t.education,"
	    			        + "school=:t.school,"
	    			        + "profession=:t.profession,"
	    			        + "money=:t.money,"
	    			        + "roomOrCar=:t.roomOrCar,"
	    			        + "isChildren=:t.isChildren,"
	    			        + "infor=:t.infor,"
	    			        + "selection=:t.selection,"
	    			        + "province=:t.province,"
	    			        + "city=:t.city,"
	    			        + "area=:t.area,"
	    			        + "updateUser=:t.updateUser,"
	    			        + "updateAt=now()"
    						+ " WHERE id=:t.id")
    void update(@SQLParam("t") MemberDetail memberDetail);
    
    @SQL("UPDATE MemberDetail SET deleteFlag=0 WHERE id=:1 ")
    void deleteById(int id);
    
    @SQL("UPDATE MemberDetail SET status=:2 WHERE id=:1 ")
    void updateStatusById(int id,int status);
}